package com.hsnn.medstgmini.pro.base.dao.Impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.model.HospInfo;
import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.base.std.model.StdDrugInfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.dao.BakpurchaseDao;
import com.hsnn.medstgmini.pro.base.model.BaseBakpurchase;
import com.hsnn.medstgmini.pro.base.model.ViewBakpurchase;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;
import com.hsnn.medstgmini.util.PinYin;

@Repository
public class BakpurchaseDaoImpl extends BaseHibernateDAO implements BakpurchaseDao {
	
	@Autowired
	private HibernateSqlUtil dao;
	
	/* 长期备案复审设置 0 - 不需要复审， 1 - 需要复审 */
	@Value("#{sysProp['bak.longterm']}")
    private String bakLongterm;
	
	/* 临时备案复审设置 0 - 不需要复审， 1 - 需要复审 */
	@Value("#{sysProp['bak.tamperary']}")
    private String bakTamperary;

	@Override
	public Pagination getAddList(Pagination page) {

		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String compCode = page.getConditions().get("compCode");
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.drug_code drugCode, c.qualityspec_name drugName, c.form_name formName, ");
		sb.append(" 	c.spec_name specName, a.factor, a.unit_name unitName, a.material_name materialName, ");
		sb.append(" 	a.comp_code compCode, a.comp_name compName ");
		sb.append(" FROM std_drugqualityspec c ");
		sb.append(" JOIN std_proddruginfo a ON a.qualityspec_id = c.qualityspec_id ");
		sb.append(" WHERE a.`status` = 1 AND a.audit_status = 1 AND c.`status` = 1 ");
		sb.append(" AND NOT EXISTS ( ");
		sb.append(" 	SELECT b.drug_code FROM base_goods_item b WHERE b.`status` = 1 AND b.proj_status = 1  ");
		sb.append(" 	AND b.proj_starttime <= SYSDATE() AND b.proj_endtime >= SYSDATE() ");
		sb.append(" 	AND b.qualityspec_status = 1 AND b.drug_status = 1 AND b.prod_compstatus = 1 ");
		sb.append(" 	AND a.drug_code = b.drug_code ");
		sb.append(" ) ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		
		if (StringUtils.isNotBlank(drugCode)) {
			sb.append(" AND a.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		
		if (StringUtils.isNotBlank(drugName)) {
			sb.append(" AND (c.qualityspec_name LIKE :drugName OR c.qualityspec_name_spel LIKE :drugName) ");
			params.put("drugName", "%" + drugName + "%");
		}
		
		if (StringUtils.isNotBlank(compCode)) {
			sb.append(" AND a.comp_code = :compCode ");
			params.put("compCode", compCode);
		}
		
		dao.getPageMapList(sb.toString(), params, page);
		
		return page;
	}
	
	@Override
	public Object getDrugInfo(String drugCode) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.drug_code drugCode, c.qualityspec_name drugName, ");
		sb.append(" 	c.form_name formName, c.spec_name specName, a.factor, ");
		sb.append(" 	a.unit_name unitName, a.material_name materialName, ");
		sb.append(" 	a.comp_code compCode, a.comp_name compName, a.approval_code approvalCode ");
		sb.append(" FROM std_drugqualityspec c ");
		sb.append(" JOIN std_proddruginfo a ON a.qualityspec_id = c.qualityspec_id ");
		sb.append(" WHERE a.`status` = 1 AND a.audit_status = 1 ");
		sb.append(" AND c.`status` = 1 AND a.drug_code = :drugCode ");
		
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.setString("drugCode", drugCode);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		return query.uniqueResult();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Pagination getCompList(Pagination page) {
		Criteria criteria = getSession().createCriteria(StdCompinfo.class, "s");// 企业
		if(StringUtils.isNotEmpty(page.getConditions().get("compCode"))){
			criteria.add(Restrictions.like("compCode", page.getConditions().get("compCode"),MatchMode.ANYWHERE));
		}
		if(StringUtils.isNotEmpty(page.getConditions().get("compName"))){
			criteria.add(Restrictions.like("compName", page.getConditions().get("compName"),MatchMode.ANYWHERE));
		}
		if(StringUtils.isNotEmpty(page.getConditions().get("compType"))){
			criteria.add(Restrictions.eq("compType", Short.valueOf(page.getConditions().get("compType"))));
		}
		Long totalCount = (Long) criteria.setProjection(Projections.rowCount())
				.uniqueResult();
		criteria.setProjection(null);
		criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		criteria.setFirstResult((page.getPage() - 1) * page.getMaxResults());
		criteria.setMaxResults(page.getMaxResults());
		List<StdDrugInfo> records = criteria.list();

		page.setRecords(totalCount);

		page.setTotal(new Long(totalCount % page.getMaxResults() == 0 ? totalCount
				/ page.getMaxResults() : totalCount / page.getMaxResults() + 1)
				.intValue());
		page.setRows(records);
		return page;
	}
	
	@Override
	public void submitFiling(BaseBakpurchase bak, SysUser user) {
		StringBuilder sb = new StringBuilder();
		sb.append(" INSERT INTO base_bakpurchase ( ");
		sb.append(" 	bak_pur_id, bak_hosp_code, drug_code, pur_price, pur_amount,  ");
		sb.append(" 	del_code, is_longtime_plan, bak_reason, remark, bak_submit_datetime, ");
		sb.append(" 	bak_submit_user, audit_status ");
		sb.append(" ) VALUES ( ");
		sb.append(" 	:bakPurId, :bakHospCode, :drugCode, :purPrice, :purAmount,  ");
		sb.append(" 	:delCode, :isLongtimePlan, :bakReason, :remark, :bakSubmitDatetime, ");
		sb.append(" 	:bakSubmitUser, 1 ");
		sb.append(" ) ");
		
		HospInfo hospInfo = user.getInfo();
		if (hospInfo == null) {
			return;
		}
		
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		
		query.setParameter("bakPurId", UUID.randomUUID().toString());
		query.setParameter("bakHospCode", hospInfo.getHospCode());
		query.setParameter("drugCode", bak.getDrugCode());
		query.setParameter("purPrice", bak.getPurPrice());
		query.setParameter("purAmount", bak.getPurAmount());
		query.setParameter("delCode", bak.getDelCode());
		query.setParameter("isLongtimePlan", bak.getIsLongtimePlan());
		query.setParameter("bakReason", bak.getBakReason());
		query.setParameter("remark", bak.getRemark());
		query.setParameter("bakSubmitDatetime", new Date());
		query.setParameter("bakSubmitUser", user.getId());
		
		query.executeUpdate();
		session.flush();
	}
	
	@Override
	public void update(BaseBakpurchase baseBakpurchase) {
		Session session=getSession();
		session.update(baseBakpurchase);
		session.flush();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Pagination getBakDurgList(Pagination page) {
		Criteria criteria = getSession().createCriteria(ViewBakpurchase.class, "s");
		
		if(StringUtils.isNotEmpty(page.getConditions().get("drugCode"))){
			criteria.add(Restrictions.eq("drugCode", page.getConditions().get("drugCode")));
		}

		/*if(StringUtils.isNotEmpty(page.getConditions().get("drugName"))){
			criteria.add(Restrictions.like("drugName", page.getConditions().get("drugName"), MatchMode.ANYWHERE));
		}*/
		if (StringUtils.isNotEmpty(page.getConditions().get("drugName"))) {
			if (PinYin.isPinYin(page.getConditions().get("drugName"))) {
				criteria.add(Restrictions.like("drugNameSpel", page
						.getConditions().get("drugName"), MatchMode.ANYWHERE));
			} else {
				criteria.add(Restrictions.like("drugName", page
						.getConditions().get("drugName"), MatchMode.ANYWHERE));
			}
		}

		if(StringUtils.isNotEmpty(page.getConditions().get("areaId"))){
			criteria.add(Restrictions.like("areaId", page.getConditions().get("areaId"), MatchMode.START));
		}
		
		if(StringUtils.isNotEmpty(page.getConditions().get("hospName"))){
			criteria.add(Restrictions.like("hospName", page.getConditions().get("hospName"),MatchMode.ANYWHERE));
		}

		/*if(StringUtils.isNotEmpty(page.getConditions().get("compName"))){
			criteria.add(Restrictions.like("compName", page.getConditions().get("compName"),MatchMode.ANYWHERE));
		}*/
		if (StringUtils.isNotEmpty(page.getConditions().get("compName"))) {
			if (PinYin.isPinYin(page.getConditions().get("compName"))) {
				criteria.add(Restrictions.like("compSpelCode", page
						.getConditions().get("compName"), MatchMode.ANYWHERE));
			} else {
				criteria.add(Restrictions.like("compName", page
						.getConditions().get("compName"), MatchMode.ANYWHERE));
			}
		}
		if(StringUtils.isNotEmpty(page.getConditions().get("startTime"))){
			criteria.add(Restrictions.ge("bakSubmitDatetime", ParseDate.fromFullFormat(page.getConditions().get("startTime")+" 00:00:00")));
		}
		
		if(StringUtils.isNotEmpty(page.getConditions().get("endTime"))){
			criteria.add(Restrictions.le("bakSubmitDatetime", ParseDate.fromFullFormat(page.getConditions().get("endTime")+" 23:59:59")));
		}
		
		if (StringUtils.isNotBlank(page.getConditions().get("auditStatus"))) {
			criteria.add(Restrictions.eq("auditStatus", Short.parseShort(page.getConditions().get("auditStatus"))));
		}
		criteria.addOrder(Order.desc("bakSubmitDatetime"));
		
		Long totalCount = (Long) criteria.setProjection(Projections.rowCount()).uniqueResult();
		criteria.setProjection(null);
		criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		criteria.setFirstResult((page.getPage() - 1) * page.getMaxResults());
		criteria.setMaxResults(page.getMaxResults());

		List<ViewBakpurchase> records = criteria.list();
		page.setRecords(totalCount);

		page.setTotal(new Long(totalCount % page.getMaxResults() == 0 ? totalCount
				/ page.getMaxResults() : totalCount / page.getMaxResults() + 1)
				.intValue());
		page.setRows(records);
		return page;
	}
	
	@Override
	public Object getBakById(String cid) {
		Criteria criteria = getSession().createCriteria(ViewBakpurchase.class, "s");// 企业
		criteria.add(Restrictions.eq("bakPurId", cid));
		return criteria.uniqueResult();
	}
	
	@Override
	public Object getBakDrugById(String bakDrugId) {
		Criteria criteria = getSession().createCriteria(BaseBakpurchase.class);
		criteria.add(Restrictions.eq("bakPurId", bakDrugId));
		return criteria.uniqueResult();
	}
	
	/**
	 * @category 卫生局审核备案
	 * @date 2015年11月21日
	 * @param bak
	 * @param user
	 */
	public void wsjAuditBak(BaseBakpurchase bak, SysUser user) {
		StringBuilder sb = new StringBuilder();
		sb.append(" UPDATE base_bakpurchase SET audit_status = :auditStatus, ");
		sb.append(" audit_remaks = :remark, audit_datetime = SYSDATE(), audit_user = :userId ");
		sb.append(" WHERE bak_pur_id = :bakPurId ");
		
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		
		if (bak.getAuditStatus() == 3) {
			if (bak.getIsLongtimePlan() == 1) {	//	长期备案
				if ("1".equals(bakLongterm)) {	//	需要复审
					query.setInteger("auditStatus", 2);
				} else {	//	不需要复审，直接审批通过
					query.setInteger("auditStatus", 3);
				}
			} else {	//	临时备案
				if ("1".equals(bakTamperary)) {	//	需要复审
					query.setInteger("auditStatus", 2);
				} else {	//	不需要复审，直接审批通过
					query.setInteger("auditStatus", 3);
				}
			}
		} else {	//	审批未通过
			query.setInteger("auditStatus", 4);
		}
		
		query.setString("remark", bak.getRemark());
		query.setString("userId", user.getId());
		query.setString("bakPurId", bak.getBakPurId());
		
		query.executeUpdate();
		session.flush();
	}
	
	/**
	 * @category 中心审核(复审)备案申报
	 */
	@Override
	public void auditBak(BaseBakpurchase bak, SysUser user, Map<String, Object> detail) {
		StringBuilder sb = new StringBuilder();
		
		sb.append(" UPDATE base_bakpurchase SET audit_status = :auditStatus, ");
		
		if (bak.getOldStatus() == 1) {	//	审核
			sb.append(" audit_remaks = :remark, audit_datetime = SYSDATE(), audit_user = :userId, ");
		} else if (bak.getOldStatus() == 2) {	//	复审
			
		}
		
		sb.append(" reaudit_remaks = :remark, reaudit_datetime = SYSDATE(), reaudit_user = :userId ");
		sb.append(" WHERE bak_pur_id = :bakPurId ");
		
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		
		query.setShort("auditStatus", bak.getAuditStatus());
		query.setString("remark", bak.getRemark());
		query.setString("userId", user.getId());
		query.setString("bakPurId", bak.getBakPurId());
		
		query.executeUpdate();
		
		if (bak.getAuditStatus() == 3) {	//	审核(复审)通过关联备案项目
			relatedProjects(detail, user);
		}
		
		session.flush();
	}
	
	/**
	 * @category 审核(复审)通过关联备案项目
	 * @date 2015年11月21日
	 * @param detail
	 */
	@SuppressWarnings("unchecked")
	private void relatedProjects(Map<String, Object> detail, SysUser user) {
		//	查询备案项目
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT proj_id projId, proj_code projCode, `name` projName ");
		sb.append(" FROM bid_project WHERE proj_cat = 5 AND `status` = 1 ");
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setMaxResults(1);
		Map<String, Object> project = (Map<String, Object>) query.uniqueResult();
		if (project != null && !project.isEmpty()) {
			relatedDrug(project, detail, user);
			relatedHosp(project, detail, user);
			relatedComp(project, detail, user);
		}
	}
	
	/**
	 * @category 备案药品关联项目
	 * @date 2015年11月21日
	 * @param project
	 * @param detail
	 */
	private void relatedDrug(Map<String, Object> project, Map<String, Object> detail, SysUser user) {
		StringBuilder sb = new StringBuilder();
		String uuid = UUID.randomUUID().toString();
		sb.append(" INSERT INTO bid_projdrugs VALUES (:uuid, :projId, :projName ");
		sb.append(" , null, null, 1, :drugCode, :compCode, 1, SYSDATE(), :userId) ");
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.setString("uuid", uuid);
		query.setString("projId", (String) project.get("projId"));
		query.setString("projName", (String) project.get("projName"));
		query.setString("drugCode", (String) detail.get("drugCode"));
		query.setString("compCode", (String) detail.get("compCode"));
		query.setString("userId", user.getId());
		query.executeUpdate();
		
		//	log
		sb = new StringBuilder();
		sb.append(" INSERT INTO bid_projdrugs_log SELECT UUID(), a.* FROM bid_projdrugs a WHERE proj_drug_id = :uuid ");
		query = getSession().createSQLQuery(sb.toString());
		query.setString("uuid", uuid);
		query.executeUpdate();
	}
	
	/**
	 * @category 备案药品的医院关联项目
	 * @date 2015年11月21日
	 * @param project
	 * @param detail
	 */
	private void relatedHosp(Map<String, Object> project, Map<String, Object> detail, SysUser user) {
		StringBuilder sb = new StringBuilder();
		String uuid = UUID.randomUUID().toString();
		sb.append(" INSERT INTO bid_projhospital VALUES (:uuid, :projId, :projName ");
		sb.append(" , null, null, 1, :hospCode, 1, SYSDATE(), :userId) ");
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.setString("uuid", uuid);
		query.setString("projId", (String) project.get("projId"));
		query.setString("projName", (String) project.get("projName"));
		query.setString("hospCode", (String) detail.get("hospCode"));
		query.setString("userId", user.getId());
		query.executeUpdate();
		
		//	log
		sb = new StringBuilder();
		sb.append(" INSERT INTO bid_projhospital_log SELECT UUID(), a.* FROM bid_projhospital a WHERE proj_hosp_id = :uuid ");
		query = getSession().createSQLQuery(sb.toString());
		query.setString("uuid", uuid);
		query.executeUpdate();
	}
	
	/**
	 * @category 备案药品的配送企业关联项目
	 * @date 2015年11月21日
	 * @param project
	 * @param detail
	 */
	private void relatedComp(Map<String, Object> project, Map<String, Object> detail, SysUser user) {
		StringBuilder sb = new StringBuilder();
		String uuid = UUID.randomUUID().toString();
		sb.append(" INSERT INTO bid_projcompany VALUES (:uuid, :projId, :projName ");
		sb.append(" , null, null, 1, :delCode, 1, 1, SYSDATE(), :userId) ");
		SQLQuery query = getSession().createSQLQuery(sb.toString());
		query.setString("uuid", uuid);
		query.setString("projId", (String) project.get("projId"));
		query.setString("projName", (String) project.get("projName"));
		query.setString("delCode", (String) detail.get("delCode"));
		query.setString("userId", user.getId());
		query.executeUpdate();
		
		//	log
		sb = new StringBuilder();
		sb.append(" INSERT INTO bid_projcompany_log SELECT UUID(), a.* FROM bid_projcompany a WHERE proj_del_id = :uuid ");
		query = getSession().createSQLQuery(sb.toString());
		query.setString("uuid", uuid);
		query.executeUpdate();
	}

}
